package com.bcs.codgen.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import com.bcs.codgen.model.*;
import org.apache.commons.lang.StringUtils;

import com.bcs.codgen.service.ColumnHandler;
import com.bcs.codgen.service.DbProvider;
import com.bcs.codgen.util.JdbcUtil;

/**
 * 针对Mysql的数据库信息提供者
 * @author 黄天政
 *
 */
public class MysqlProvider extends DbProvider {
	private static final long serialVersionUID = 3208056888252225777L;
	/**
	 * 针对mysql特有的扩展属性键——数据类型（不带数据长度和小数位数），键值示例：bit、bigint、varchar、longtext、float
	 */
	public static final String EXT_KEY_DATA_TYPE = "DATA_TYPE";
	/**
	 * 针对mysql特有的扩展属性键——列类型（带数据长度和小数位数），键值示例：bit(1)、bigint(20)、varchar(500)、longtext、float(5,1)
	 */
	public static final String EXT_KEY_COLUMN_TYPE = "COLUMN_TYPE";
	/**
	 * 针对mysql特有的扩展属性键——列键（主键和索引），键值示例：PRI(主键)、UNI(唯一索引)、MUL(普通索引)
	 */
	public static final String EXT_KEY_COLUMN_KEY = "COLUMN_KEY";
	/**
	 * 针对mysql特有的扩展属性键——列的排序位置（从1开始的自然数）
	 */
	public static final String EXT_KEY_ORDINAL_POSITION = "ORDINAL_POSITION";

	public MysqlProvider(Connection conn) {
		super(conn);
	}

	public MysqlProvider(JdbcConfig jdbcConfig) {
		super(jdbcConfig);
	}


	@Override
	protected Map<String, String> doGetColumnComments(String tableName) {
		Map<String, String> colComment = new LinkedHashMap<String, String>();
		String columnName = null, comment = null;
		Statement stmt = null;
		ResultSet rs = null;
		//如果数据库限定名不为空，则加上作为前缀
		if(StringUtils.isNotBlank(getCatalog()) && !tableName.contains(".")){
			tableName = getCatalog() + "." + tableName;
		}
		String sql = "show full fields from "+tableName;
		try{
			stmt = getConn().createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				columnName = rs.getString("Field");
				comment = StringUtils.trim(rs.getString("Comment"));
				colComment.put(columnName, comment);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			JdbcUtil.safelyClose(rs, stmt);
		}
		return colComment;
	}

	@Override
	protected Map<String, String> doGetTableComments() {
		Map<String, String> tableComments = new LinkedHashMap<String, String>();
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.tables WHERE 1=1 ";
		//加上数据库名过滤
		String catalog = getCatalog();
		if(StringUtils.isBlank(catalog)){
			catalog = getCatalog4CurrentConn();
		}
		//注：mysql的数据库限定名跟jdbc反过来用了，在information_schema中是TABLE_SCHEMA字段
		sql += String.format(" AND TABLE_SCHEMA='%s' ", catalog);

		String tnps = getTableNamePatterns();
		if(StringUtils.isNotBlank(tnps)){
			List<String> conditions = new ArrayList<>();
			for (String tnp : tnps.split(",")) {
				conditions.add("TABLE_NAME LIKE '%"+tnp+"%'");
			}
			sql += " AND (" + StringUtils.join(conditions, " OR ") + ")";
		}

		try{
			stmt = getConn().createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				tableComments.put(rs.getString("TABLE_NAME"), rs.getString("TABLE_COMMENT")) ;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			JdbcUtil.safelyClose(rs, stmt);
		}
		return tableComments;
	}

	/**
	 * 取得当前连接的catalog（即数据库名称）
	 * @return
	 */
	private String getCatalog4CurrentConn(){
		String catalog = null;
		try {
			catalog = getConn().getCatalog();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return catalog;
	}

	/**
	 * 构建查询指定表中一条记录的SQL语句（不同数据库方言语法可能不同）
	 *
	 * @param tableName 表名
	 * @return 可以执行查询返回一条记录的sql语句
	 */
	@Override
	protected String buildSql4OneRecord(String tableName) {
		return "select * from " + tableName + " limit 1";
	}

	/**
	 * 根据指定的表名创建一个表模型
	 *
	 * @param tableName 表名称
	 * @return
	 * @throws SQLException
	 */
	@Override
	public TableModel createTableModel(String tableName) {
		TableModel tableModel = super.createTableModel(tableName);
		//按列名索引列模型
		Map<String, ColumnModel> columnModelMap = new HashMap<>();
		for (ColumnModel columnModel : tableModel.getColumnList()) {
			columnModelMap.put(columnModel.getColumnName(), columnModel);
		}

		Statement stmt = null;
		ResultSet rs = null;
		String sql = String.format("SELECT * FROM information_schema.columns WHERE TABLE_NAME ='%s' ", tableName);
		//加上数据库名过滤
		String catalog = getCatalog();
		if(StringUtils.isBlank(catalog)){
			catalog = getCatalog4CurrentConn();
		}
		//注：mysql的数据库限定名跟jdbc反过来用了，在information_schema中是TABLE_SCHEMA字段
		sql += String.format(" AND TABLE_SCHEMA='%s' ", catalog);

		try{
			stmt = getConn().createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				String columnName = rs.getString("COLUMN_NAME");
				ColumnModel columnModel = columnModelMap.get(columnName);
				//设置列的默认值
				columnModel.setDefaultValue(rs.getString("COLUMN_DEFAULT"));
				//扩展属性-数据类型（不带数据长度和小数位数），键值示例：bit、bigint、varchar、longtext、float
				columnModel.getExt().put(EXT_KEY_DATA_TYPE, rs.getString(EXT_KEY_DATA_TYPE));
				//扩展属性-列类型（带数据长度和小数位数），键值示例：bit(1)、bigint(20)、varchar(500)、longtext、float(5,1)
				columnModel.getExt().put(EXT_KEY_COLUMN_TYPE, rs.getString(EXT_KEY_COLUMN_TYPE));
				//扩展属性-列键（主键和索引），键值示例：PRI(主键)、UNI(唯一索引)、MUL(普通索引)
				columnModel.getExt().put(EXT_KEY_COLUMN_KEY, rs.getString(EXT_KEY_COLUMN_KEY));
				//扩展属性-列的排序位置（从1开始的自然数）
				columnModel.getExt().put(EXT_KEY_ORDINAL_POSITION, rs.getInt(EXT_KEY_ORDINAL_POSITION));
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			JdbcUtil.safelyClose(rs, stmt);
		}

		return tableModel;
	}
}
